package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.jdbc.Statement;
import com.proactive.webapp.dao.CardDAO;
import com.proactive.webapp.entity.Card;
import com.proactive.webapp.entity.Member;

public class CardDAOImpl implements CardDAO{
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public int insert(Card card){
		int cardID = 0;
		String sql = "INSERT INTO card(cardTemplateID,activityID,cardTitle,cardImage,"
				+ "cardContent,cardStatus,memberAccount,createDate) VALUES(?,?,?,?,?,?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			smt.setInt(1, card.getTid());
			smt.setInt(2, card.getAid());
			smt.setString(3, card.getTitle());
			smt.setString(4, card.getImagePath());
			smt.setString(5, card.getContent());
			smt.setInt(6, card.getStatus());
			smt.setString(7, card.getAccount());
			smt.executeUpdate();
			rs = smt.getGeneratedKeys();
			if(rs.next()){
				cardID = rs.getInt(1);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return cardID;
	}

	@Override
	public int countTotal(int activityID) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM card WHERE activityID = ? AND cardStatus = 1";
		try {			
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, activityID);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Card> getList(int activityID, int start, int size) {
		// TODO Auto-generated method stub
		List<Card> cardList = new ArrayList<Card>();
		String sql = "SELECT * FROM card WHERE activityID = ? AND cardStatus = 1 ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, activityID);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Card card = new Card();
				card.setId(rs.getInt("cardID"));
				card.setTitle(rs.getString("cardTitle"));
				card.setImagePath(rs.getString("cardImage"));
				cardList.add(card);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return cardList;
	}

	@Override
	public Card get(Card card) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM card a JOIN member b ON a.memberAccount = b.memberAccount "
				+ "WHERE cardID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, card.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setMemberNO(rs.getString("memberNO"));
				member.setName(rs.getString("memberName"));
				card.setMember(member);
				card.setTitle(rs.getString("cardTitle"));
				card.setImagePath(rs.getString("cardImage"));
				card.setContent(rs.getString("cardContent"));
				card.setStatus(rs.getInt("cardStatus"));
				card.setShow(card.getStatus()==1?true:false);
				card.setAid(rs.getInt("activityID"));
				card.setTid(rs.getInt("cardTemplateID"));
				card.setAccount(rs.getString("memberAccount"));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return card;
	}

	@Override
	public int countTotalByMember(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM card WHERE memberAccount = ? AND cardStatus = 1";
		try {			
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Card> getListByMember(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Card> cardList = new ArrayList<Card>();
		String sql = "SELECT * FROM card WHERE memberAccount = ? AND cardStatus = 1 ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Card card = new Card();
				card.setId(rs.getInt("cardID"));
				card.setTitle(rs.getString("cardTitle"));
				card.setImagePath(rs.getString("cardImage"));
				cardList.add(card);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return cardList;
	}

	@Override
	public void update(Card card) {
		// TODO Auto-generated method stub
		String sql = "UPDATE card SET cardTitle=?, cardImage=?, cardContent=?, "
				+ "cardStatus = ? WHERE cardID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, card.getTitle());
			smt.setString(2, card.getImagePath());
			smt.setString(3, card.getContent());
			smt.setInt(4, card.getStatus());
			smt.setInt(5, card.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public void delete(Card card) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM card WHERE cardID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, card.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public int countTotalByWriter(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM card WHERE memberAccount = ?";
		try {			
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Card> getListByWriter(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Card> cardList = new ArrayList<Card>();
		String sql = "SELECT * FROM card WHERE memberAccount = ? ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Card card = new Card();
				card.setId(rs.getInt("cardID"));
				card.setTitle(rs.getString("cardTitle"));
				card.setImagePath(rs.getString("cardImage"));
				card.setStatus(rs.getInt("cardStatus"));
				card.setActString(card.getStatus()==2?"顯示":"隱藏");
				card.setActClass(card.getStatus()==2?"show":"hide");
				card.setHideClass(card.getStatus()==2?"edithide":"");
				cardList.add(card);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return cardList;
	}

	@Override
	public void updateStatus(Card card) {
		// TODO Auto-generated method stub
		String sql = "UPDATE card SET cardStatus = ? WHERE cardID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, card.getStatus());
			smt.setInt(2, card.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	

}
